





/*
 * ALTER  the procedure to update Sales Audit Trail
 * according to the modifications done.(Sales_Audit_Trail)
 */
ALTER                        proc sp_save_salesorder_audit_trail
/*
 * input parameters
 */
@salesOrder_id                  int,
@SO_Customer_PO_Number 		varchar(50),
@SO_Contact_Person 		varchar(50),
@SO_Sales_Person 		varchar(50),
@SO_Shipping_Method 		varchar(50),
@SO_Shipping_Account 		varchar(50),--6
@SO_Comments 			varchar(500),--7
@SO_Shipping_Instructions 	varchar(500),
@SO_EndUser 			varchar(50),
@SO_Sales_Tax_Type 		varchar(50),
@SO_Sub_Total 			float,--11
@SO_Sales_Tax 			float,
@SO_Frieght_Tax 		float,
@SO_Misc 			float,
@Regional_Rep 			varchar(50),--15
@Shipping_Terms 		varchar(50),
@Delivery_Terms 		varchar(50),
@user_id			varchar(50),
@CSO_Reason			varchar(50),--19
@SO_Shipping_Address_ID 	int,
@SO_Booking_Date	        varchar(50),
@SO_Misc_Name			varchar(50),
@SO_Billing_Address_ID 		int,
@Phone				varchar(50),
@Fax				varchar(50),
@Email				varchar(50)
AS
/*
 * variable for getting actual values
 */
declare @db_SO_Customer_PO_Number 	varchar(50)
declare @db_SO_Contact_Person 		varchar(50)
declare @db_SO_Sales_Person 		varchar(50)
declare @db_SO_Shipping_Method 		varchar(50)
declare @db_SO_Shipping_Account 	varchar(50)
declare @db_SO_Comments 		varchar(500)
declare @db_SO_Shipping_Instructions 	varchar(500)
declare @db_SO_EndUser 			varchar(50)
declare @db_SO_Sales_Tax_Type 		varchar(50)
declare @db_SO_Sub_Total 		float
declare @db_SO_Sales_Tax 		float
declare @db_SO_Frieght_Tax 		float
declare @db_SO_Misc 			float
--declare @db_SalesOrder_No 		varchar(50)
declare @db_Regional_Rep 		varchar(50)
declare @db_Shipping_Terms 		varchar(50)
declare @db_Delivery_Terms 		varchar(50)
declare @db_SO_Booking_date 		datetime
declare @db_SO_Misc_Name		varchar(50)
declare @db_SO_Billing_Address_ID 	int
declare @db_SalesOrder_ID		int
declare @db_CSO_Reason			varchar(50)
declare @db_ChangeNo                    varchar(10)
declare @db_SO_Shipping_Address_ID 	int
declare @db_Phone			varchar(50)
declare @db_Fax				varchar(50)
declare @db_Email			varchar(50)

declare @totalChanges                   varchar(8000)
declare @totalChanges1                  varchar(8000)
declare @SO_ChangeNo			int

declare @comments                       varchar(500)
declare @oldTax                         varchar(50)
declare @newTax                         varchar(50)
declare @tempOldTax varchar(50)
declare @tempNewTax varchar(50)
--SubTotal values
declare @tempOldTotal varchar(50)
declare @tempNewTotal varchar(50)
--SalesTax Values
declare @tempOldSalesTax varchar(50)
declare @tempNewSalesTax varchar(50)
--Frieght Values
declare @oldFrieght varchar(50)
declare @newFrieght varchar(50)
--Misc values
declare @oldMisc varchar(50)
declare @newMisc varchar(50)
--Old and New Addresses
declare @oldAddress  varchar(200)
declare @oldAddress1 varchar(200)
declare @oldAddress2 varchar(200)
declare @oldCity     varchar(200)
declare @oldState    varchar(200)
declare @oldZipcode  varchar(200)
declare @oldCountry  varchar(200)
declare @newAddress  varchar(200)
declare @newAddress1 varchar(200)
declare @newAddress2 varchar(200)
declare @newCity     varchar(200)
declare @newState    varchar(200)
declare @newZipcode  varchar(200)
declare @newCountry  varchar(200)
--Booking Date 
--declare @date varchar(50)
/*
 * Getting actual values into variables
 */

--print @Phone+''+@Fax+''+@Email
SELECT @db_ChangeNo = max(ChangeNo) from Change_Sales_Order where SalesOrder_ID = @salesOrder_id
SET @SO_ChangeNo = @db_ChangeNo - 1
--print @SO_ChangeNo 
--print @db_ChangeNo

SELECT @db_SO_Customer_PO_Number = CSO_Customer_PO_Number, @db_SO_Contact_Person = CSO_Contact_Person,
@db_SO_Sales_Person = CSO_Sales_Person, @db_SO_Shipping_Method =  CSO_Shipping_Method, @db_SO_Shipping_Account = CSO_Shipping_Account,
@db_SO_Comments =  CSO_Comments,@db_SO_Shipping_Instructions = CSO_Shipping_Instructions, @db_SO_EndUser = CSO_EndUser,
@db_SO_Sales_Tax_Type =  CSO_Sales_Tax_Type, @db_SO_Sub_Total =  CSO_Sub_Total, @db_SO_Sales_Tax =  CSO_Sales_Tax,
@db_SO_Frieght_Tax =  CSO_Frieght_Tax, @db_SO_Misc =  CSO_Misc,@db_Regional_Rep =  Regional_Rep , @db_Shipping_Terms =  Shipping_Terms,
@db_Delivery_Terms =  Delivery_Terms , @db_SO_Booking_date =  CSO_Booking_date ,@db_SO_Misc_Name = CSO_Misc_Name, @db_SO_Comments = CSO_Comments ,
@db_SO_Shipping_Instructions = CSO_Shipping_Instructions ,@db_CSO_Reason = CSO_Reason ,@db_SO_Shipping_Address_ID = CSO_Shipping_Address_ID,
@db_SO_Billing_Address_ID = CSO_Billing_Address_ID
FROM Change_Sales_Order WHERE SalesOrder_ID = @salesOrder_id and ChangeNo = @db_ChangeNo

/* Getting New Address and Old Address*/
SELECT @newAddress1 = Address_1, @newAddress2 = Address_2, @newCity = City, @newState = State, @newZipcode = Zipcode, @newCountry = Country
FROM Address WHERE Address_ID = @db_SO_Shipping_Address_ID
SET @newAddress = @newAddress1+' , '+@newAddress2+' , '+@newCity+' , '+@newState+' - '+@newZipcode+' , '+@newCountry
declare @bookingDate varchar(50)

--Added by Sarada 12/12/2003 to update audit trail if we change PhoneNo,Fax and Email
SELECT @db_Phone = Phone_Number, @db_Fax = Fax_Number, @db_Email = Email FROM Address WHERE Address_ID = @db_SO_Billing_Address_ID

-- vasanth 08/22/2003--A very minor change---format had to be in (101) instead of 1--begin
SELECT @bookingDate = convert(varchar(50), @db_SO_Booking_date, 101)
-- vasanth 08/22/2003--A very minor change---format had to be in (101) instead of 1--end

SELECT @tempOldTax = Tax FROM Sales_Tax_Table WHERE 
                 State = @SO_Sales_Tax_Type
SELECT @oldTax =  str(@tempOldTax,8,2)
SELECT @tempNewTax = Tax FROM Sales_Tax_Table WHERE 
                 State = @db_SO_Sales_Tax_Type
select @newTax =  str(@tempNewTax,8,2)
/*SubTotal Values*/
select @tempOldTotal = str(@SO_Sub_Total,12,2)
select @tempNewTotal = str(@db_SO_Sub_Total,12,2)
/*SalesTax Values*/
select @tempOldSalesTax = str(@SO_Sales_Tax,12,2)
select @tempNewSalesTax = str(@db_SO_Sales_Tax,12,2)
/* Frieght Values*/
select @oldFrieght = str(@SO_Frieght_Tax,12,2)
select @newFrieght = str(@db_SO_Frieght_Tax,12,2)
/* Misc values*/
select @oldMisc = str(@SO_Misc,12,2)
select @newMisc = str(@db_SO_Misc,12,2)


/*
 * Check if there is any change in the sales order 
*/

declare @comparision varchar(10)
declare @firstValue varchar(8000)
SET @totalChanges = ''

if(@db_CSO_Reason is NULL or ltrim(rtrim(@db_CSO_Reason)) = '')
    SET @comparision = 'FALSE'
else
    SET @comparision = 'TRUE'
if(@comparision = 'TRUE')
	SET @totalChanges = @totalChanges+'<br> Reason is <b><I>'+@db_CSO_Reason +'</I></b>'

if((@SO_Customer_PO_Number <> @db_SO_Customer_PO_Number) or (@SO_Customer_PO_Number is NULL))
    SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges+'<br> CustomerPO is changed from <b><I>'+@SO_Customer_PO_Number+'</I></b> to <b><I>'+@db_SO_Customer_PO_Number +'</I></b>'

if((@SO_Contact_Person <> @db_SO_Contact_Person) or (@SO_Contact_Person is NULL))
 SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> ContactPerson is changed from <b><I>'+@SO_Contact_Person+' </I></b> to <b><I>'+@db_SO_Contact_Person +'</I></b>'

if((@Phone <> @db_Phone) or (@Phone is NULL))
 SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Phone is changed from <b><I>'+@Phone+' </I></b> to <b><I>'+@db_Phone +'</I></b>'

if((@Fax <> @db_Fax) or (@Fax is NULL))
 SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Fax is changed from <b><I>'+@Fax+' </I></b> to <b><I>'+@db_Fax +'</I></b>'

if((@Email <> @db_Email) or (@Email is NULL))
 SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Email is changed from <b><I>'+@Email+' </I></b> to <b><I>'+@db_Email +'</I></b>'

if((@SO_Sales_Person <> @db_SO_Sales_Person) or (@SO_Sales_Person is NULL))
 SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> SalesPerson is changed from <b><I>'+@SO_Sales_Person+' </I></b> to <b><I>'+@db_SO_Sales_Person +'</I></b>'

if((@SO_Shipping_Method <> @db_SO_Shipping_Method)or (@SO_Shipping_Method is NULL))
 SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Shipping Method is changed from <b><I>'+@SO_Shipping_Method+' </I></b> to <b><I>'+@db_SO_Shipping_Method +'</I></b>'

if((@SO_Shipping_Account <> @db_SO_Shipping_Account)or (@SO_Shipping_Account is NULL))
 SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Shipping Account is changed from <b><I>'+@SO_Shipping_Account+' </I></b> to <b><I>'+@db_SO_Shipping_Account +'</I></b>'

if((@SO_EndUser <> @db_SO_EndUser)or (@SO_EndUser is NULL))
SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> End User is changed from <b><I>'+@SO_EndUser+' </I></b> to <b><I>'+@db_SO_EndUser +'</I></b>'

if((@SO_Sales_Tax_Type <> @db_SO_Sales_Tax_Type)or (@SO_Sales_Tax_Type is NULL))
SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Sales Tax Type is changed from <b><I>'+@SO_Sales_Tax_Type+' - '+@oldTax+' </I></b> to <b><I>'+@db_SO_Sales_Tax_Type+' - '+ @newTax +'</I></b>'  

if(@SO_Sub_Total <> @db_SO_Sub_Total)
begin

--SET @totalChanges = @totalChanges +'<br> SubTotal is changed from <b><I>'+@tempOldTotal+' </I></b> to <b><I>'+@tempNewTotal +'</I></b>'
	if(@totalChanges is not NULL)
		SET @totalChanges = @totalChanges +'<br> SubTotal is changed from <b><I>'+@tempOldTotal+' </I></b> to <b><I>'+@tempNewTotal +'</I></b>'
	else
	begin
            	SET @totalChanges = ''   
		SET @totalChanges1 = '<br> SubTotal is changed from <b><I>'+@tempOldTotal+' </I></b> to <b><I>'+@tempNewTotal +'</I></b>'
		SET @totalChanges = @totalChanges + @totalChanges1  

	end
		print @totalChanges

end
if(@SO_Sales_Tax <> @db_SO_Sales_Tax)
begin
	if(@totalChanges is not NULL)
		SET @totalChanges = @totalChanges +'<br> SalesTax is changed from <b><I>'+@tempOldSalesTax+' </I></b> to <b><I>'+@tempNewSalesTax +'</I></b>'
	else
	begin
            	SET @totalChanges = ''   
		SET @totalChanges1 = '<br> SalesTax is changed from <b><I>'+@tempOldSalesTax+' </I></b> to <b><I>'+@tempNewSalesTax +'</I></b>'
		SET @totalChanges = @totalChanges + @totalChanges1  
	end
	print @totalChanges
end

if(@SO_Frieght_Tax <> @db_SO_Frieght_Tax)
SET @totalChanges = @totalChanges +'<br> Frieght is changed from <b><I>'+@oldFrieght+' </I></b>to <b><I>'+@newFrieght +'</I></b>'
if(@SO_Misc <> @db_SO_Misc)
SET @totalChanges = @totalChanges +'<br> Misc is changed from <b><I>'+@oldMisc+'</I></b> to <b><I>'+@newMisc +'</I></b>'


if((@SO_Misc_Name <> @db_SO_Misc_Name)or (@SO_Misc_Name is NULL))
SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Misc Name is changed from <b><I>'+@SO_Misc_Name+' </I></b> to <b><I>'+@db_SO_Misc_Name +'</I></b>'


if((@Regional_Rep <> @db_Regional_Rep)or (@Regional_Rep is NULL))
	SET @comparision = 'TRUE'
else
    	SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Regional Rep is changed from <b><I>'+@Regional_Rep+' </I></b> to <b><I>'+@db_Regional_Rep +'</I></b>'

if((@Shipping_Terms <> @db_Shipping_Terms)or (@Shipping_Terms is NULL))
	SET @comparision = 'TRUE'
else
    	SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Pay Terms are changed from <b><I>'+@Shipping_Terms+' </I></b> to <b><I>'+@db_Shipping_Terms +'</I></b>'
if((@Delivery_Terms <> @db_Delivery_Terms)or (@Delivery_Terms is NULL))
SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Delivery Terms are changed from <b><I>'+@Delivery_Terms+' </I></b> to <b><I>'+@db_Delivery_Terms +'</I></b>'
if((@SO_Booking_Date = @bookingDate)or (@bookingDate is NULL))
    SET @comparision = 'FALSE'
else
    SET @comparision = 'TRUE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges+'<br> Booking Date is changed from <b><I>'+@SO_Booking_Date+' </I></b> to <b><I>'+@bookingDate +'</I></b>'
if((@SO_Shipping_Instructions <> @db_SO_Shipping_Instructions)or (@SO_Shipping_Instructions is NULL))
SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @totalChanges = @totalChanges +'<br> Shipping Instructions are changed from <b><I>'+@SO_Shipping_Instructions+' </I></b> to <b><I>'+@db_SO_Shipping_Instructions +'</I></b>'
if(@SO_Shipping_Address_ID <> @db_SO_Shipping_Address_ID)
SET @totalChanges = @totalChanges +'<br> Shipping Address is Changed to <b><I>'+@newAddress +'</I></b>'

/* Comments comparision*/
if((@SO_Comments <> @db_SO_Comments)or (@SO_Comments is NULL))
SET @comparision = 'TRUE'
else
    SET @comparision = 'FALSE'
if(@comparision = 'TRUE')
SET @comments = '<br> Comments are changed from <b><I>'+@SO_Comments+' </I></b> to <b><I>'+@db_SO_Comments +'</I></b>'
else
SET @comments = ''


--SET @totalChanges = ''

if (@totalChanges is not NULL and @totalChanges <> '')
begin
if(@db_ChangeNo <> 0)
	SET @firstValue = 'Change Number is <b><I>'+@db_ChangeNo +'</I></b>' 
else
	SET @firstValue = '' 
	set @firstValue = @firstValue + @totalChanges
end
else
  begin
	--if (@db_CSO_Reason is not NULL and ltrim(rtrim(@db_CSO_Reason)) = '')
	--SET @firstValue = 'Change Number is <b><I>'+@db_ChangeNo +'</I></b>'  
	--else 
        --set @firstValue = ''
	--set @db_CSO_Reason = ''
        --SET @firstValue = @db_CSO_Reason
        if (@comments is not NULL and @comments <> '')
	SET @firstValue = 'Change Number is <b><I>'+@db_ChangeNo +'</I></b>'  
	else 
        set @firstValue = ''
  end

--print '@firstValue'+@firstValue

INSERT INTO Sales_Audit_Trail(SalesOrder_ID,User_ID,Actions,Comments,TimeStamp)
VALUES(@salesorder_id,@user_id,@firstValue,@comments,CURRENT_TIMESTAMP)   






